﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using Excel10 = Microsoft.Office.Interop.Excel;
using System.Reflection;
using MossmanMill.DataModel;
using System.Runtime.InteropServices;
using System.Globalization;
using System.Threading;

namespace MossmanMill.Controller
{
    /// <summary>
    /// Data import class that imports excel data into database
    /// </summary>
    /// <remarks>
    /// This class is thread safe. 
    /// </remarks>
    /// <dates>
    /// 15/05/2012 - created by Chiaki Sato
    /// </dates>
    class ImportManager : IImportManager
    {

        // Excel Sheet Name
        private const string SHEET_NAME_EQUIPMENT_LIST = "Equipment List";
        private const string SHEET_NAME_C200_RACKS = "C200 RACKS";

        // Equipment List Worksheet Columns
        private const int COLUMN_ITEM_NUMBER = 1;
        private const int COLUMN_QUANTITY = 2;
        private const int COLUMN_DESCRIPTION = 3;
        private const int COLUMN_PART_NUMBER = 4;
        private const int COLUMN_MANUFACTURE = 5;
        private const int COLUMN_VENDER = 6;
        private const int COLUMN_NOTES = 7;

        // C200 RACKS Worksheet Columns
        private const int COLUMN_ROOM_NAME = 1;

        // Equipment List Worksheet Rows
        private const int ROW_EQUIPMENT_DATA_START = 9;
        private const int ROW_EQUIPMENT_DATA_END = 100;

        // C200 RACKS Worksheet Rows
        private const int ROW_ROOM_DATA_START = 2;
        private const int ROW_ROOM_DATA_END = 350;
        private const int ROW_OFFSET_SLOT_PART_NUMBER = 3;
        private const int ROW_OFFSET_SLOT_NETWORK_CONNECTION = 7;
        private const int ROW_OFFSET1_SLOT_ID_NAME = 8;
        private const int ROW_OFFSET2_SLOT_ID_NAME = 9;

        // Rack Model Name
        private const string RACK_MODEL_NAME_C200 = "C200";

        // Network Connection Type
        private const string NETWORK_CONNECTION_TYPE_SERVER = "SERVER";

        // Sensor Worksheet Name
        private string[] CPM_WORKSHEET_NAME = { "CPM0101","CPM0301","CPM0501","CPM0701","CPM0901" };
        private const string CPM0101 = "CPM0101";
        private const string CPM0301 = "CPM0301";
        private const string CPM0501 = "CPM0501";
        private const string CPM0701 = "CPM0701";
        private const string CPM0901 = "CPM0901";

        // CPM101 Worksheet Columns
        private const int COLUMN_CPM0101_TAG_NO = 1;
        private const int COLUMN_CPM0101_PARAM = 2;
        private const int COLUMN_CPM0101_DESCRIPTION = 3;
        private const int COLUMN_CPM0101_SLOT_ID_NAME = 4;
        private const int COLUMN_CPM0101_SLOT = 7;
        private const int COLUMN_CPM0101_CHANNEL = 8;

        // CPM101 Worksheet Rows
        private const int ROW_CPM0101_DATA_START = 5;
        private const int ROW_CPM0101_DATA_END = 400;

        // CPM301 Worksheet Columns
        private const int COLUMN_CPM0301_TAG_NO = 1;
        private const int COLUMN_CPM0301_PARAM = 3;
        private const int COLUMN_CPM0301_DESCRIPTION = 2;
        private const int COLUMN_CPM0301_SLOT_ID_NAME = 8;
        private const int COLUMN_CPM0301_SLOT = 7;
        private const int COLUMN_CPM0301_CHANNEL = 9;

        // CPM301 Worksheet Rows
        private const int ROW_CPM0301_DATA_START = 11;
        private const int ROW_CPM0301_DATA_END = 500;

        // CPM501 Worksheet Columns
        private const int COLUMN_CPM0501_TAG_NO = 1;
        private const int COLUMN_CPM0501_PARAM = 2;
        private const int COLUMN_CPM0501_DESCRIPTION = 3;
        private const int COLUMN_CPM0501_SLOT_ID_NAME = 4;
        private const int COLUMN_CPM0501_SLOT = 7;
        private const int COLUMN_CPM0501_CHANNEL = 8;

        // CPM501 Worksheet Rows
        private const int ROW_CPM0501_DATA_START = 7;
        private const int ROW_CPM0501_DATA_END = 700;

        // CPM701 Worksheet Columns
        private const int COLUMN_CPM0701_TAG_NO = 1;
        private const int COLUMN_CPM0701_PARAM = 2;
        private const int COLUMN_CPM0701_DESCRIPTION = 3;
        private const int COLUMN_CPM0701_SLOT_ID_NAME = 4;
        private const int COLUMN_CPM0701_SLOT = 7;
        private const int COLUMN_CPM0701_CHANNEL = 8;

        // CPM701 Worksheet Rows
        private const int ROW_CPM0701_DATA_START = 6;
        private const int ROW_CPM0701_DATA_END = 1100;

        // CPM901 Worksheet Columns
        private const int COLUMN_CPM0901_TAG_NO = 1;
        private const int COLUMN_CPM0901_PARAM = 2;
        private const int COLUMN_CPM0901_DESCRIPTION = 3;
        private const int COLUMN_CPM0901_SLOT_ID_NAME = 4;
        private const int COLUMN_CPM0901_SLOT = 7;
        private const int COLUMN_CPM0901_CHANNEL = 8;

        // CPM901 Worksheet Rows
        private const int ROW_CPM0901_DATA_START = 7;
        private const int ROW_CPM0901_DATA_END = 500;

        private String excelFileDiretory;

        /// <summary>
        /// Constructor for Import form class. 
        /// </summary>
        public ImportManager(String excelFileDiretory)
        {
            this.excelFileDiretory = excelFileDiretory;
        }

        /// <summary>
        /// Import equipment data into database. 
        /// </summary>
        /// <param name="senderWorkerThread">Sender Worker Thread</param>
        public void EquipmentDataImport(BackgroundWorker senderWorkerThread)
        {

            try
            {
                Excel10.Application excel;
                excel = new Excel10.Application();
                Excel10.Workbook workBook;

                workBook = (Excel10.Workbook)excel.Workbooks.Open(
                excelFileDiretory,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing);

                string sheetName = SHEET_NAME_EQUIPMENT_LIST;
                Excel10.Worksheet sheet;
                sheet = (Excel10.Worksheet)workBook.Sheets[getSheetIndex(sheetName, workBook.Sheets)];

                Excel10.Range range;
                List<Equipment> equipmentList = new List<Equipment>();

                for (int i = 0; i < ROW_EQUIPMENT_DATA_END; i++)
                {
                    range = (Excel10.Range)sheet.Cells[ROW_EQUIPMENT_DATA_START + i, COLUMN_PART_NUMBER];
                    string partNumber = range.Text.ToString();
                    range = (Excel10.Range)sheet.Cells[ROW_EQUIPMENT_DATA_START + i, COLUMN_DESCRIPTION];
                    string descritption = range.Text.ToString();

                    partNumber = partNumber.Trim();
                    descritption = descritption.Trim();

                    if (partNumber.Length > 0)
                    {
                        Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + "partNumber = " + partNumber + " descritption = " + descritption);

                        equipmentList.Add(new Equipment(partNumber, descritption));
                    }
                }

                senderWorkerThread.ReportProgress(5);       // 5% progress to complete

                workBook.Close(false, Type.Missing, Type.Missing);
                excel.Quit();

                // Create Database Access by Factory
                IDBFactory dbFactory = new DBFactory();
                IDatabaseAccess db = dbFactory.Make(DBFactory.CLASS_TYPE_DATABASE);

                foreach (Equipment equipment in equipmentList)
                {

                    int ioType = 4;
                    int numberOfChannel =8;

                    if (equipment.PartNumber.Length > 5)
                    {
                        // Get IO type
                        int posHyphen = equipment.PartNumber.IndexOf("-");
                        if (posHyphen >= 0)
                        {
                            /// <param name="type">DO,DI,AO,AI,OTHER</param>
                            /// <param name="channels">Number of channels</param>

                            String ioTypeString = equipment.PartNumber.Substring(posHyphen + 1, 2);
                            if (ioTypeString.CompareTo((@"IA")) == 0)
                            {
                                ioType = 3;
                            }
                            else if (ioTypeString.CompareTo((@"OA")) == 0)
                            {
                                ioType = 2;
                            }
                            else if (ioTypeString.CompareTo((@"ID")) == 0)
                            {
                                ioType = 1;
                            }
                            else if (ioTypeString.CompareTo((@"OD")) == 0)
                            {
                                ioType = 0;
                            }
                            else
                            {
                                ioType = 4;
                            }
                        }

                        // Get number of channel
                        int posNumberOfChannel = equipment.PartNumber.Length - 3;
                        String numberOfChannelString = equipment.PartNumber.Substring(posNumberOfChannel, 2);
                        try
                        {
                            numberOfChannel = int.Parse(numberOfChannelString);
                        }
                        catch (Exception ex)
                        {   // Not Number
                            numberOfChannel = 8;
                            Exception dummy = ex;
                        }
                    }
                    db.AddIOModule(equipment.PartNumber, ioType, numberOfChannel, equipment.Descritption);
                }

                senderWorkerThread.ReportProgress(10);       // 10% progress to complete

            }

            catch (COMException excption)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + excption.ToString());
                throw excption;
            }
            catch (Exception excption)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + excption.ToString());
                throw excption;
            }
        }

        /// <summary>
        /// Import room and rack data into database. 
        /// </summary>
        /// <param name="senderWorkerThread">Sender Worker Thread</param>
        public void RoomRackDataImport(BackgroundWorker senderWorkerThread)
        {

            try
            {
                Excel10.Application excel;
                excel = new Excel10.Application();
                Excel10.Workbook workBook;

                workBook = (Excel10.Workbook)excel.Workbooks.Open(
                excelFileDiretory,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing);

                string sheetName = SHEET_NAME_C200_RACKS;
                Excel10.Worksheet sheet;
                sheet = (Excel10.Worksheet)workBook.Sheets[getSheetIndex(sheetName, workBook.Sheets)];

                Excel10.Range range;
                List<RackRoom> rackRoomList = new List<RackRoom>();

                for (int i = 0; i < ROW_ROOM_DATA_END; i++)
                {
                    range = (Excel10.Range)sheet.Cells[ROW_ROOM_DATA_START + i, COLUMN_ROOM_NAME];
                    string rackModelRoomName = range.Text.ToString();

                    rackModelRoomName = rackModelRoomName.Trim();

                    if (rackModelRoomName.Contains(RACK_MODEL_NAME_C200))
                    {
                        string roomName = RemoveBracket(rackModelRoomName);

                        if(roomName != "")
                        {   // Room name exists

                        //int startRomeNameBracket = rackModelRoomName.IndexOf("(");
                        //int endRomeNameBracket = rackModelRoomName.IndexOf(")");

                        //if (startRomeNameBracket != 1 && endRomeNameBracket != 1)
                        //{   // Brackets exist

                        //    int startRomeName = startRomeNameBracket + 1;
                        //    int roomNameLength = endRomeNameBracket - startRomeNameBracket - 1;
                        //    string roomName = rackModelRoomName.Substring(startRomeName, roomNameLength);

                            // Adjust case
                            roomName = roomName.ToLower();
                            CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
                            TextInfo textInfo = cultureInfo.TextInfo;
                            roomName = textInfo.ToTitleCase(roomName);

                            int rack_model_name_length = RACK_MODEL_NAME_C200.Length;

                            int startRackName = rack_model_name_length + 1;
                            int startRomeNameBracket = rackModelRoomName.IndexOf("(");
                            int rackNameLength = startRomeNameBracket - startRackName - 1;
                            string rackName = rackModelRoomName.Substring(startRackName, rackNameLength);
                            rackName = rackName.Trim();
                            if (rackName.Length > 0)
                            {

                                // Read Slots and Card information                                
                                List<CardSlot> cardSlotList = new List<CardSlot>();
                                for (int position = 0; position < 12; position++)
                                {
                                    int cellColumn = position + 1;

                                    // Read Part Number
                                    int cellRowCard = ROW_ROOM_DATA_START + ROW_OFFSET_SLOT_PART_NUMBER + i;
                                    Excel10.Range rangePart = (Excel10.Range)sheet.Cells[cellRowCard, cellColumn];
                                    string partNumber = rangePart.Text.ToString();
                                    partNumber = partNumber.Trim();

                                    // Read Slot Id Name
                                    int cellRowSlotIdName = ROW_ROOM_DATA_START + ROW_OFFSET1_SLOT_ID_NAME + i;
                                    Excel10.Range rangeSlotIdName = (Excel10.Range)sheet.Cells[cellRowSlotIdName, cellColumn];
                                    string slotIdName = rangeSlotIdName.Text.ToString();
                                    slotIdName = slotIdName.Trim();

                                    if (slotIdName == "")       // this code for the inconsistancy of row position of Slot id name
                                    {
                                        cellRowSlotIdName = ROW_ROOM_DATA_START + ROW_OFFSET2_SLOT_ID_NAME + i;
                                        rangeSlotIdName = (Excel10.Range)sheet.Cells[cellRowSlotIdName, cellColumn];
                                        slotIdName = rangeSlotIdName.Text.ToString();
                                        slotIdName = slotIdName.Trim();
                                    }

                                    // Read Network Connection
                                    int cellRowNetworkConnection = ROW_ROOM_DATA_START + ROW_OFFSET_SLOT_NETWORK_CONNECTION + i;
                                    Excel10.Range rangeNetworkConnection = (Excel10.Range)sheet.Cells[cellRowNetworkConnection, cellColumn];
                                    string networkConnection = rangeNetworkConnection.Text.ToString();
                                    networkConnection = networkConnection.Trim();
                                    networkConnection = RemoveBracket(networkConnection);

                                    cardSlotList.Add(new CardSlot(partNumber, position, slotIdName, networkConnection));
                           
                                }

                                // Create Rack Room informaiton
                                rackRoomList.Add(new RackRoom(rackName, roomName, cardSlotList));

                            }
                        }
                    }

                }
                senderWorkerThread.ReportProgress(18);       // 18% progress to complete

                workBook.Close(false, Type.Missing, Type.Missing);
                excel.Quit();

                // Create Database Access by Factory
                IDBFactory dbFactory = new DBFactory();
                IDatabaseAccess db = dbFactory.Make(DBFactory.CLASS_TYPE_DATABASE);

                foreach (RackRoom rackRoom in rackRoomList)
                {
                    db.AddRoom(rackRoom.RoomName, "0,0");
                }

                List<DataModel.Room> dbRoomList = db.GetRooms();

                foreach (RackRoom rackRoom in rackRoomList)
                {
                    foreach (DataModel.Room dbRoom in dbRoomList)
                    {
                        if (dbRoom.Name.CompareTo(rackRoom.RoomName) == 0)
                        {
                            db.AddRack(dbRoom.Id, RACK_MODEL_NAME_C200, rackRoom.RackName);
                        }
                    }
                }

                senderWorkerThread.ReportProgress(32);       // 32% progress to complete

                List<DataModel.Rack> dbRackList = db.GetRacks();

                foreach (DataModel.Rack dbRack in dbRackList)
                {
                    foreach (RackRoom rackRoom in rackRoomList)
                    {
                        if ((rackRoom.RackName == dbRack.Address) &&
                            (rackRoom.RoomName == dbRack.Room.Name))
                        {
                            // Get dababase record IDs for Rack
                            rackRoom.RackId = dbRack.Id;
                                                        
                            // Get dababase record IDs for IOModule (Equipment)
                            foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                            {

                                List<Card> dbCardList;
                                dbCardList = db.GetAvailableCards();

                                foreach (Card dbCard in dbCardList)
                                {

                                    if (dbCard.Name == cardSlot.PartNumber)
                                    {
                                        cardSlot.PartId = dbCard.ModuleId;
                                    }
                                }
                            }
                        }
                    }
                }

                foreach (RackRoom rackRoom in rackRoomList)
                {
                    int rackId = rackRoom.RackId;

                    foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                    {
                        int partID = cardSlot.PartId;
                        int position = cardSlot.Position;

                        string cardType;

                        if (cardSlot.PartNumber.Length > 2)
                        {
                            cardType = cardSlot.PartNumber.Substring(0, 2);
                        }
                        else
                        {   // Empty
                            cardType = "";
                        }

                        switch (cardType)
                        {
                            // Network
                            case "CC":

                                string networkConnectionType = cardSlot.NetworkConnectionType;

                                if (networkConnectionType == NETWORK_CONNECTION_TYPE_SERVER)
                                {
                                    db.AddNetworkCard(rackId, position, cardSlot.PartNumber, 'U', 0);
                                }
                                else
                                {
                                    int parentCardId = 0;

                                    if (rackRoom.RackName.Contains("RTU"))
                                    {
                                        if (rackRoom.RackName.Contains(CPM0101))
                                        {
                                            parentCardId = getParentCardId(CPM0101, rackRoomList);
                                        }
                                        else if (rackRoom.RackName.Contains(CPM0301))
                                        {
                                            parentCardId = getParentCardId(CPM0301, rackRoomList);
                                        }
                                        else if (rackRoom.RackName.Contains(CPM0501))
                                        {
                                            parentCardId = getParentCardId(CPM0501, rackRoomList);
                                        }
                                        else if (rackRoom.RackName.Contains(CPM0701))
                                        {
                                            parentCardId = getParentCardId(CPM0701, rackRoomList);
                                        }
                                        else if (rackRoom.RackName.Contains(CPM0901))
                                        {
                                            parentCardId = getParentCardId(CPM0901, rackRoomList);
                                        }
                                    }

                                    db.AddNetworkCard(rackId, position, cardSlot.PartNumber, 'D', parentCardId);
                                }
                                break;

                            // Processor
                            case "PR":

                                db.AddProcessor(rackId, position, cardSlot.PartNumber);
                                break;

                            // Empty
                            case "":
                                break;

                            // Card
                            default:

                                // Check if partID exist
                                if (partID != 0)
                                {
                                    // Add slotle and card data into database
                                    db.AddCard(rackId, position, partID);
                                }
                                break;
                        }
                    }
                }
                senderWorkerThread.ReportProgress(40);       // 40% progress to complete

                SensorDataImport(senderWorkerThread, workBook, db, rackRoomList);

                senderWorkerThread.ReportProgress(100);       // 100% progress to complete
            }    

            catch (COMException exception)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + exception.ToString());
                throw exception;

            }
            catch (Exception exception)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + exception.ToString());
                throw exception;
            }

        }

        /// <summary>
        /// Import Sensor data into database. 
        /// </summary>
        /// <param name="workBook">workBook</param>
        private void SensorDataImport(BackgroundWorker senderWorkerThread, Excel10.Workbook workBook2222, IDatabaseAccess db, List<RackRoom> rackRoomList)
        {
            List<Card> dbCardList = new List<Card>();

            dbCardList = db.GetCards();

            foreach (Card dbCard in dbCardList)
            {
                int dbRackId = dbCard.RackId;
                int dbSlotId = dbCard.SlotId;
                int dbSlotPosition = dbCard.Position;
                int dbCardId = dbCard.CardId; 

                foreach (RackRoom rackRoom in rackRoomList)
                {
                    int rackId = rackRoom.RackId;

                    if (rackId == dbRackId)
                    {

                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                        {                            
                            int position = cardSlot.Position;

                            if (position == dbSlotPosition)
                            {
                                cardSlot.DbCardId = dbCardId;
                            }
                        }
                    }
                } 
            }


            try
            {
                Excel10.Application excel;
                excel = new Excel10.Application();

                Excel10.Workbook workBook = (Excel10.Workbook)excel.Workbooks.Open(
                excelFileDiretory,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing);
                
                foreach (string sheetName in CPM_WORKSHEET_NAME)
                {
                    Excel10.Worksheet sheet;

                    sheet = (Excel10.Worksheet)workBook.Sheets[getSheetIndex(sheetName, workBook.Sheets)];
                    Excel10.Range range;

                    switch(sheetName)
                    {
                        case CPM0101:

                            for (int i = ROW_CPM0101_DATA_START; i < ROW_CPM0101_DATA_END; i++)
                            {
                                range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_SLOT_ID_NAME];
                                string slotIdName = range.Text.ToString();
                                slotIdName = slotIdName.Trim();

                                if (slotIdName.Length > 8)
                                {
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_TAG_NO];
                                    string tagNo = range.Text.ToString();
                                    tagNo = tagNo.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_PARAM];
                                    string param = range.Text.ToString();
                                    param = param.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_DESCRIPTION];
                                    string description = range.Text.ToString();
                                    description = description.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_SLOT];
                                    string slotNumber = range.Text.ToString();
                                    slotNumber = slotNumber.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0101_CHANNEL];
                                    string channelString = range.Text.ToString();
                                    channelString = channelString.Replace("Ch.", "");       // Remove prefix Ch.
                                    channelString = channelString.Trim();
                                    int channel = int.Parse(channelString);                 // Convert to integer
                                    
                                    foreach (RackRoom rackRoom in rackRoomList)
                                    {

                                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                                        {
                                            if (slotIdName == cardSlot.SlotIdName)
                                            {
                                                if (cardSlot.DbCardId != 0)
                                                {
                                                    db.AddSensor(new Sensor(-1,tagNo,param,description,channel,cardSlot.DbCardId));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            senderWorkerThread.ReportProgress(52);       // 52% progress to complete
                            break;

                        case CPM0301:
                            for (int i = ROW_CPM0301_DATA_START; i < ROW_CPM0301_DATA_END; i++)
                            {
                                range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_SLOT_ID_NAME];
                                string slotIdName = range.Text.ToString();
                                slotIdName = slotIdName.Trim();

                                if (slotIdName.Length > 8)
                                {
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_TAG_NO];
                                    string tagNo = range.Text.ToString();
                                    tagNo = tagNo.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_PARAM];
                                    string param = range.Text.ToString();
                                    param = param.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_DESCRIPTION];
                                    string description = range.Text.ToString();
                                    description = description.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_SLOT];
                                    string slotNumber = range.Text.ToString();
                                    slotNumber = slotNumber.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0301_CHANNEL];
                                    string channelString = range.Text.ToString();
                                    channelString = channelString.Replace("Ch.", "");       // Remove prefix Ch.
                                    channelString = channelString.Trim();
                                    int channel = int.Parse(channelString);                 // Convert to integer

                                    foreach (RackRoom rackRoom in rackRoomList)
                                    {

                                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                                        {
                                            if (slotIdName == cardSlot.SlotIdName)
                                            {
                                                if (cardSlot.DbCardId != 0)
                                                {
                                                    db.AddSensor(new Sensor(-1,tagNo,param,description,channel,cardSlot.DbCardId));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            senderWorkerThread.ReportProgress(64);       // 64% progress to complete
                            break;

                        case CPM0501:
                            for (int i = ROW_CPM0501_DATA_START; i < ROW_CPM0501_DATA_END; i++)
                            {
                                range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_SLOT_ID_NAME];
                                string slotIdName = range.Text.ToString();
                                slotIdName = slotIdName.Trim();

                                if (slotIdName.Length > 8)
                                {
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_TAG_NO];
                                    string tagNo = range.Text.ToString();
                                    tagNo = tagNo.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_PARAM];
                                    string param = range.Text.ToString();
                                    param = param.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_DESCRIPTION];
                                    string description = range.Text.ToString();
                                    description = description.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_SLOT];
                                    string slotNumber = range.Text.ToString();
                                    slotNumber = slotNumber.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0501_CHANNEL];
                                    string channelString = range.Text.ToString();
                                    channelString = channelString.Replace("Ch.", "");       // Remove prefix Ch.
                                    channelString = channelString.Trim();
                                    int channel = int.Parse(channelString);                 // Convert to integer

                                    foreach (RackRoom rackRoom in rackRoomList)
                                    {

                                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                                        {
                                            if (slotIdName == cardSlot.SlotIdName)
                                            {
                                                if (cardSlot.DbCardId != 0)
                                                {
                                                    db.AddSensor(new Sensor(-1,tagNo,param,description,channel,cardSlot.DbCardId));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            senderWorkerThread.ReportProgress(76);       // 76% progress to complete
                            break;

                        case CPM0701:
                            for (int i = ROW_CPM0701_DATA_START; i < ROW_CPM0701_DATA_END; i++)
                            {
                                range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_SLOT_ID_NAME];
                                string slotIdName = range.Text.ToString();
                                slotIdName = slotIdName.Trim();

                                if (slotIdName.Length > 8)
                                {
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_TAG_NO];
                                    string tagNo = range.Text.ToString();
                                    tagNo = tagNo.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_PARAM];
                                    string param = range.Text.ToString();
                                    param = param.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_DESCRIPTION];
                                    string description = range.Text.ToString();
                                    description = description.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_SLOT];
                                    string slotNumber = range.Text.ToString();
                                    slotNumber = slotNumber.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0701_CHANNEL];
                                    string channelString = range.Text.ToString();
                                    channelString = channelString.Replace("Ch.", "");       // Remove prefix Ch.
                                    channelString = channelString.Trim();
                                    int channel = int.Parse(channelString);                 // Convert to integer

                                    foreach (RackRoom rackRoom in rackRoomList)
                                    {

                                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                                        {
                                            if (slotIdName == cardSlot.SlotIdName)
                                            {
                                                if (cardSlot.DbCardId != 0)
                                                {
                                                    db.AddSensor(new Sensor(-1,tagNo,param,description,channel,cardSlot.DbCardId));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            senderWorkerThread.ReportProgress(88);       // 88% progress to complete
                            break;

                        case CPM0901:
                            for (int i = ROW_CPM0901_DATA_START; i < ROW_CPM0901_DATA_END; i++)
                            {
                                range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_SLOT_ID_NAME];
                                string slotIdName = range.Text.ToString();
                                slotIdName = slotIdName.Trim();

                                if (slotIdName.Length > 8)
                                {
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_TAG_NO];
                                    string tagNo = range.Text.ToString();
                                    tagNo = tagNo.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_PARAM];
                                    string param = range.Text.ToString();
                                    param = param.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_DESCRIPTION];
                                    string description = range.Text.ToString();
                                    description = description.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_SLOT];
                                    string slotNumber = range.Text.ToString();
                                    slotNumber = slotNumber.Trim();
                                    range = (Excel10.Range)sheet.Cells[i, COLUMN_CPM0901_CHANNEL];
                                    string channelString = range.Text.ToString();
                                    channelString = channelString.Replace("Ch.", "");       // Remove prefix Ch.
                                    channelString = channelString.Trim();
                                    int channel = int.Parse(channelString);                 // Convert to integer

                                    foreach (RackRoom rackRoom in rackRoomList)
                                    {

                                        foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                                        {
                                            if (slotIdName == cardSlot.SlotIdName)
                                            {
                                                if (cardSlot.DbCardId != 0)
                                                {
                                                    db.AddSensor(new Sensor(-1, tagNo, param, description, channel, cardSlot.DbCardId));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            break;
                    }

                }
            }
            catch (COMException exception)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + exception.ToString());
                throw exception;

            }
            catch (Exception exception)
            {
                Console.WriteLine(MethodBase.GetCurrentMethod().Name + ": " + exception.ToString());
                throw exception;
            }
        }


        /// <summary>
        /// Delete All Table Data for Inital Data Loading
        /// </summary>
        public void DeleteAllTableData()
        {
            // Create Database Access by Factory
            IDBFactory dbFactory = new DBFactory();
            IDatabaseAccess db = dbFactory.Make(DBFactory.CLASS_TYPE_DATABASE);

            db.DeleteAllTableData();
        }

        private int getSheetIndex(string sheetName, Excel10.Sheets sheets)
        {
            int i = 0;
            foreach (Excel10.Worksheet sheet in sheets)
            {
                if (sheetName == sheet.Name)
                {
                    return i + 1;
                }
                i += 1;
            }
            return 0;
        }

        private string RemoveBracket(string bracketText)
        {
            string test = "";

            int startBracketText = bracketText.IndexOf("(");
            int endBracketText = bracketText.IndexOf(")");

            if ((startBracketText != -1 && endBracketText != -1) &&
                (startBracketText != 1 && endBracketText != 1))
            {   // Brackets exist

                int textStartPos = startBracketText + 1;
                int textLength = endBracketText - startBracketText - 1;
                test = bracketText.Substring(textStartPos, textLength);
            }
            return test;
        }

        private int getParentCardId(string parentRackName, List<RackRoom> rackRoomList)
        {
            int parentCardId = 0;

            foreach (RackRoom rackRoom in rackRoomList)
            {
                if (rackRoom.RackName == parentRackName)
                {
                    foreach (CardSlot cardSlot in rackRoom.cardSlotList)
                    {
                        if (cardSlot.PartNumber.Length > 2)
                        {
                            string cardTypeSub = cardSlot.PartNumber.Substring(0, 2);
                            if (cardTypeSub == "CC")
                            {
                                if (cardSlot.Position == 3)
                                {
                                    parentCardId = cardSlot.DbCardId;
                                }
                            }
                        }
                    }
                }
            }

            return parentCardId;
        }


        private class Equipment : Object
        {
            private string partNumber;
            private string descritption;

            public string PartNumber
            {
                get { return partNumber; }
                set { partNumber = value; }
            }

            public string Descritption
            {
                get { return descritption; }
                set { descritption = value; }
            }

            public Equipment(string partNumber, string descritption)
            {
                this.partNumber = partNumber;
                this.descritption = descritption;
            }
        }

        private class RackRoom : Object
        {
            private string roomName;
            private string rackName;
            private int rackId;

            public List<CardSlot> cardSlotList;

            public RackRoom(string rackName, string roomName, List<CardSlot> cardSlotList)
            {
                this.roomName = roomName;
                this.rackName = rackName;
                this.cardSlotList = cardSlotList;
            }

            public string RoomName
            {
                get { return roomName; }
                set { roomName = value; }
            }

            public string RackName
            {
                get { return rackName; }
                set { rackName = value; }
            }

            public int RackId
            {
                get { return rackId; }
                set { rackId = value; }
            }
        }

        private class CardSlot : Object
        {
            private string partNumber;
            private int position;
            private string slotIdName;         // ex. BSAIM070104
            private string networkConnectionType;

            // Database record Ids
            private int partId;
            private int dbSlotId;
            private int dbCardId;

            public CardSlot(string partNumber, int position, string slotIdName, string networkConnectionType)
            {
                this.partNumber = partNumber;
                this.position = position;
                this.slotIdName = slotIdName;
                this.networkConnectionType = networkConnectionType;
            }

            public string PartNumber
            {
                get { return partNumber; }
                set { partNumber = value; }
            }
            public int Position
            {
              get { return position; }
              set { position = value; }
            }
            public int PartId
            {
              get { return partId; }
              set { partId = value; }
            }
            public string SlotIdName
            {
                get { return slotIdName; }
                set { slotIdName = value; }
            }
            public string NetworkConnectionType
            {
                get { return networkConnectionType; }
                set { networkConnectionType = value; }
            }
            public int DbCardId
            {
                get { return dbCardId; }
                set { dbCardId = value; }
            }
            public int DbSlotId
            {
                get { return dbSlotId; }
                set { dbSlotId = value; }
            }

        }

    }
}
